

using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.IO;
using System.Data;
using System.Data.SqlServerCe;
using System.Security.Principal;
using System.Security.AccessControl;
namespace GeneralClasses
{

    //larger container class that holds all of the data for a specific day

    public class DatabaseConnection
    {

        #region variables
        //==============================================================================
        //                                                                     VARIABLES
        //==============================================================================

        String connectionString;
        //SqlConnection connection; // it shoudl be made, used, and then shut, so no need to keep it

        //ResultSet resultSet; // no need for this either
        #endregion

        #region properties
        //==============================================================================
        //                                                                    PROPERTIES
        //==============================================================================

        public String ConnectionString
        {
            get { return connectionString; }
            set { connectionString = value; }
        }


        #endregion

        #region constructor
        //==============================================================================
        //                                                                   CONSTRUCTOR
        //==============================================================================
        public DatabaseConnection()
        {
            initialize();

        }

        public DatabaseConnection(String connectionString)
        {
            initialize();
            this.connectionString = connectionString;
        }

        private void initialize()
        {

            //Local database connection strings

            //String dataSource = @"Data Source=HYRULE-LAKE\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";


            //eric
            //String dataSource = @"Data Source=BALTHESAR\SQLEXPRESS;Initial Catalog=master;Integrated Security=True";
            //String dataSource=@"C:\Documents and Settings\Eric\My Documents\CODEAGE\C\C#\cmpsc483wgroupe\NurseScheduler\DevDataLibrary\Dev.sdf";

            //Online Winhost database
            //String dataSource = @"Data Source=tcp:s01.winhost.com;Initial Catalog=DB_15732_groupe;User ID=DB_15732_groupe_user;Password=pennstate;Integrated Security=False;";

            //David Local
            //String dataSource = @"Data Source=DAVID-PC\SQLEXPRESS;Initial Catalog=TestDatabase;Integrated Security=True";

            //Dev.sdf
            //String dataSource = @"Data Source=..\..\..\DevDataLibrary\Dev3.sdf; Persist Security Info=False;";
            //String dataSource = "this is a fake one to test if the error messages get passed back up";

            //DevBig.sdf
            String dataSource = @"Data Source=..\..\..\DevDataLibrary\DevBig.sdf; Persist Security Info=False;";


            connectionString = dataSource;
            //connectionString = "Data Source = " + dataSource + "; User ID = " + username + "; Password = " + password + "; Initial Catalog = " + initialCatalog + ";";
        }

        #endregion


        #region public
        //==============================================================================
        //                                                                        PUBLIC
        //==============================================================================


        /// <summary>
        /// inserts the command into teh database
        /// </summary>
        /// <param name="insertionString"></param>
        public void executeNonQuery(String insertionString)
        {
            try
            {
                if (this.connectionString.Contains(".sdf"))
                {
                    //using statement automatically closes connection
                    using (SqlCeConnection connection = new SqlCeConnection(this.connectionString))
                    {
                        connection.Open();

                        using (SqlCeCommand cmd = new SqlCeCommand(insertionString, connection))
                        {
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
                else
                {
                    //using statement automatically closes connection
                    using (SqlConnection connection = new SqlConnection(this.connectionString))
                    {
                        connection.Open();

                        using (SqlCommand cmd = new SqlCommand(insertionString, connection))
                        {
                            cmd.ExecuteNonQuery();
                        }
                    }

                }
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                //UTIL.sayError(e.Message);
                //UTIL.say(e.StackTrace);
                throw e;
                //throw e; // this doesnt work find out how it works
            }
        }

        /// <summary>
        /// receives the query and returns the results with a SqlDataReader class
        /// </summary>
        /// <param name="query">Query to be passed to the database</param>
        /// <returns>Returns SqlDataReader object with results</returns>
        public DataTable query(String query)
        {


            try
            {
                DataTable dataTable = new DataTable();

                if (this.connectionString.Contains(".sdf"))
                {
                    //using statement automatically closes connection and reader
                    using (SqlCeConnection connection = new SqlCeConnection(this.connectionString))
                    {
                        connection.Open();

                        using (SqlCeCommand cmd = new SqlCeCommand(query, connection))
                        {
                            using (SqlCeDataReader reader = cmd.ExecuteReader())
                            {
                                dataTable.Load(reader);
                            }
                        }
                    }
                }
                else
                {
                    //using statement automatically closes connection and reader
                    using (SqlConnection connection = new SqlConnection(this.connectionString))
                    {
                        connection.Open();

                        using (SqlCommand cmd = new SqlCommand(query, connection))
                        {
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                dataTable.Load(reader);
                            }
                        }
                        connection.Close();
                    }
                }
                return dataTable;
            }
            catch (Exception e)
            {
                UTIL.sayDebug(e);
                //UTIL.sayError(e.Message);
                //UTIL.say(e.StackTrace);
                throw e;
            }
        }


        //ifthe resultset isnt null, it will return the number of items in it
        public int deprecated_getCount()
        {
            //return resultSet.count();
            return -1;
        }

        public void deprecated_setData()
        {
            /*if((connectionString!=null)&&(query!=null)){
                MySqlCommand cmdDatabase = new MySqlCommand(query, connectionString);
                connection.Open();
                cmdDatabase.ExecuteNonQuery();
                connection.Close;
			
                return something;
            } else return null;
            */
        }

        public void deprecated_getData()
        {
            /*
            if((connectionString!=null)&&(query!=null)){
                MySqlCommand cmdDatabase = new MySqlCommand(query, connectionString);
                connection.Open();
                cmdDatabase.ExecuteQuery();
                connection.Close();
		
                return something;
            } else return  null;*/
        }

        #endregion

    } // end class


} //end namespace